﻿using BP.Template.Printer;
using System;
using System.ComponentModel.DataAnnotations;
using System.IO;
using ClosedXML.Excel; //NuGet安装ClosedXML库：
using System.Linq;
using System.Data;
using System.Collections.Generic;
using System.Xml;
using System.Xml.Linq;
using System.Xml.XPath;
using Spire.Doc.Documents;
using Spire.Doc;
using Spire.Doc.Fields;
using System.Formats.Asn1;
using BP.Tools;
using Newtonsoft.Json;
using NPOI.SS.Formula.Functions;
using DocumentFormat.OpenXml.Math;

namespace BP.Sys.Printer
{
    public class OfficerHelper
    {
        public FrmPrintTemplate HisFrmPrintTemplate = null;
        /// <summary>
        /// 数据源
        /// </summary>
        public DB HisDB = null;
        public string FrmID = null;
        public void MakeDocFile(string frmID, string templateFile, FrmPrintTemplate template, Int64 workID)
        {
            this.FrmID = frmID;
            this.HisFrmPrintTemplate = template;

            //生成数据源.
            this.HisDB = new BP.Sys.Printer.DB(template.FrmID, template.FrmID, template.MyPK, workID);

            var ss = this.HisDB.HisDBEles;
            var ss2 = this.HisDB.HisDBDtls;

            Spire.Doc.Document sdoc = new Spire.Doc.Document();
            sdoc.LoadFromFile(templateFile);

            //新的表单检测
            DataItemClass MainTags = GetMainTags(sdoc);
            foreach (ItemClass item in MainTags.tagItems)
            {
                if (item.type == MyitemEnum.TagInline) {
                    //文本中字段赋值
                    SetTagInlineValue(template, sdoc, item);
                }else if (item.type == MyitemEnum.TagCell)
                {
                    //表格中字段赋值
                    SetTagCellValue(template, sdoc, item);
                }
                else if (item.type == MyitemEnum.Tag)
                {
                    //块字段赋值
                    SetTagValue(template, sdoc, item);
                }
                else if (item.type == MyitemEnum.Table)
                {
                    //单选字段赋值
                    SetTagTableValue(template, sdoc, item);
                }
                else if (item.type == MyitemEnum.ChildTable)
                {
                    //从表赋值
                    SetChildTableValue(template, sdoc, item);
                }
            }
            sdoc.SaveToFile(templateFile);

        }

        /// <summary>
        /// SetTagInlineValue赋值操作
        /// </summary>
        /// <param name="template"></param>
        /// <param name="sdoc"></param>
        /// <param name="item"></param>
        public void SetTagInlineValue(FrmPrintTemplate template, Spire.Doc.Document sdoc, ItemClass item )
        {
            StructureDocumentTagInline TagInline = item.taginline;
            string _lable = TagInline.SDTProperties.Alias;
            string _tag = TagInline.SDTProperties.Tag;
            string _text = TagInline.SDTContent.Text;

            //CheckBoxItem$YangPinMingChen.0
            if (_tag.IndexOf("MetaData$") == -1) return;
            if (_tag.IndexOf("|Title") > 0) return;

            int txt_type = 0;
            string ctl_KeyOfEn = "";
            if (_tag.IndexOf("|Value") == -1)
            {
                if (_tag.IndexOf("|Data") == -1 && _tag.IndexOf("|Time") == -1)
                {
                    return;
                }
                else
                {
                    if (_tag.IndexOf("|Data") > 0)
                    {
                        ctl_KeyOfEn = _tag.Replace("MetaData$", "").Replace("|Data", "");
                        txt_type = 1;
                    }
                    if (_tag.IndexOf("|Time") > 0)
                    {
                        ctl_KeyOfEn = _tag.Replace("MetaData$", "").Replace("|Time", "");
                        txt_type = 2;
                    }
                }
            }
            else
            {
                ctl_KeyOfEn = _tag.Replace("MetaData$", "").Replace("|Value", "");
            }

            //编号错误跳过
            if (string.IsNullOrEmpty(ctl_KeyOfEn)) return;

            string val = "";
            val = this.HisDB.HisDBEles.GetValByKey(template.FrmID, ctl_KeyOfEn).AttrValue.ToString();
            if (string.IsNullOrEmpty(val))
            {
                TagInline.ChildObjects.Clear();
                TextRange rt = new TextRange(sdoc);
                rt.Text = " ";
                TagInline.ChildObjects.Clear();
                TagInline.ChildObjects.Add(rt);
            }
            else
            {
                if (txt_type == 0)
                {
                    if (TagInline.SDTProperties.SDTType == Spire.Doc.Documents.SdtType.Text)
                    {
                        if (!string.IsNullOrEmpty(val))
                        {
                            TextRange rt = new TextRange(sdoc);
                            rt.Text = val;
                            TagInline.ChildObjects.Clear();
                            TagInline.ChildObjects.Add(rt);
                        }
                        else
                        {
                            TagInline.ChildObjects.Clear();
                        }
                    }

                    if (TagInline.SDTProperties.SDTType == Spire.Doc.Documents.SdtType.DropDownList)
                    {
                        SdtDropDownList stList = TagInline.SDTProperties.ControlProperties as SdtDropDownList;
                        string _v = "";
                        foreach (SdtListItem it in stList.ListItems)
                        {
                            if (it.DisplayText == val)
                            {
                                _v = it.Value;
                                break;
                            }
                        }
                        if (_v != "")
                        {
                            TextRange rt = new TextRange(sdoc);
                            rt.Text = val;
                            TagInline.ChildObjects.Clear();
                            TagInline.ChildObjects.Add(rt);
                        }
                    }
                    if (TagInline.SDTProperties.SDTType == Spire.Doc.Documents.SdtType.DatePicker)
                    {
                        string[] arrtime = val.Split(' ');
                        TextRange rt = new TextRange(sdoc);
                        rt.Text = arrtime[0];
                        TagInline.ChildObjects.Clear();
                        TagInline.ChildObjects.Add(rt);
                    }
                }
                else if (txt_type == 1)
                {
                    string[] arrtime = val.Split(' ');
                    if (TagInline.SDTProperties.SDTType == Spire.Doc.Documents.SdtType.DatePicker)
                    {
                        TextRange rt = new TextRange(sdoc);
                        rt.Text = arrtime[0];
                        TagInline.ChildObjects.Clear();
                        TagInline.ChildObjects.Add(rt);
                    }
                }
                else if (txt_type == 2)
                {
                    string[] arrtime = val.Split(' ');
                    if (arrtime.Length == 2)
                    {
                        SdtDropDownList stList = TagInline.SDTProperties.ControlProperties as SdtDropDownList;
                        string _v = "";
                        foreach (SdtListItem it in stList.ListItems)
                        {
                            if (it.DisplayText == arrtime[1])
                            {
                                _v = it.Value;
                                break;
                            }
                        }
                        if (_v != "")
                        {
                            TextRange rt = new TextRange(sdoc);
                            rt.Text = arrtime[1];
                            TagInline.ChildObjects.Clear();
                            TagInline.ChildObjects.Add(rt);
                        }
                    }
                }
            }
        }

        /// <summary>
        /// SetTagCellValue赋值操作
        /// </summary>
        /// <param name="template"></param>
        /// <param name="sdoc"></param>
        /// <param name="item"></param>
        public void SetTagCellValue(FrmPrintTemplate template, Spire.Doc.Document sdoc, ItemClass item)
        {
            StructureDocumentTagCell TagCell = item.tagCell;
            string _lable = TagCell.SDTProperties.Alias;
            string _tag = TagCell.SDTProperties.Tag;
            //string _text = TagCell.SDTContent.Text;

            //CheckBoxItem$YangPinMingChen.0
            if (_tag.IndexOf("MetaData$") == -1) return;
            if (_tag.IndexOf("|Title") > 0) return;

            int txt_type = 0;
            string ctl_KeyOfEn = "";
            if (_tag.IndexOf("|Value") == -1)
            {
                if (_tag.IndexOf("|Data") == -1 && _tag.IndexOf("|Time") == -1)
                {
                    return;
                }
                else
                {
                    if (_tag.IndexOf("|Data") > 0)
                    {
                        ctl_KeyOfEn = _tag.Replace("MetaData$", "").Replace("|Data", "");
                        txt_type = 1;
                    }
                    if (_tag.IndexOf("|Time") > 0)
                    {
                        ctl_KeyOfEn = _tag.Replace("MetaData$", "").Replace("|Time", "");
                        txt_type = 2;
                    }
                }
            }
            else
            {
                ctl_KeyOfEn = _tag.Replace("MetaData$", "").Replace("|Value", "");
            }  

            //编号错误跳过
            if (string.IsNullOrEmpty(ctl_KeyOfEn)) return;

            string val = "";
            val = this.HisDB.HisDBEles.GetValByKey(template.FrmID, ctl_KeyOfEn).AttrValue.ToString();


            if (string.IsNullOrEmpty(val))
            {
                //Tag.ChildObjects.Clear();
                TextRange rt = new TextRange(sdoc);
                rt.Text = " ";
                //Tag.ChildObjects.Clear();
                TagCell.ChildObjects.Add(rt);
            }
            else
            {
                if (txt_type == 0)
                {
                    if (TagCell.SDTProperties.SDTType == Spire.Doc.Documents.SdtType.Text)
                    {
                        if (TagCell.ChildObjects.Count == 1)
                        {
                            DocumentObject cellobj = TagCell.ChildObjects[0];
                            if (cellobj.DocumentObjectType == DocumentObjectType.Paragraph)
                            {
                                Spire.Doc.Documents.Paragraph pobj = (cellobj as Spire.Doc.Documents.Paragraph);
                                pobj.Text = val;

                            }
                        }
                    }

                    if (TagCell.SDTProperties.SDTType == Spire.Doc.Documents.SdtType.DropDownList)
                    {
                        SdtDropDownList stList = TagCell.SDTProperties.ControlProperties as SdtDropDownList;
                        string _v = "";
                        foreach (SdtListItem it in stList.ListItems)
                        {
                            if (it.DisplayText == val)
                            {
                                _v = it.Value;
                                break;
                            }
                        }

                        if (TagCell.ChildObjects.Count == 1)
                        {
                            DocumentObject cellobj = TagCell.ChildObjects[0];
                            if (cellobj.DocumentObjectType == DocumentObjectType.Paragraph)
                            {
                                Spire.Doc.Documents.Paragraph pobj = (cellobj as Spire.Doc.Documents.Paragraph);
                                pobj.Text = val;

                            }
                        }
                    }
                    if (TagCell.SDTProperties.SDTType == Spire.Doc.Documents.SdtType.DatePicker)
                    {
                        string[] arrtime = val.Split(' ');
                        //TextRange rt = new TextRange(sdoc);
                        //rt.Text = arrtime[0];
                        //Tag.ChildObjects.Clear();
                        //Tag.ChildObjects.Add(rt);

                        if (TagCell.ChildObjects.Count == 1)
                        {
                            DocumentObject cellobj = TagCell.ChildObjects[0];
                            if (cellobj.DocumentObjectType == DocumentObjectType.Paragraph)
                            {
                                Spire.Doc.Documents.Paragraph pobj = (cellobj as Spire.Doc.Documents.Paragraph);
                                pobj.Text = arrtime[0];

                            }
                        }

                    }
                }
                else if (txt_type == 1)
                {
                    string[] arrtime = val.Split(' ');
                    if (TagCell.SDTProperties.SDTType == Spire.Doc.Documents.SdtType.DatePicker)
                    {
                        if (TagCell.ChildObjects.Count == 1)
                        {
                            DocumentObject cellobj = TagCell.ChildObjects[0];
                            if (cellobj.DocumentObjectType == DocumentObjectType.Paragraph)
                            {
                                Spire.Doc.Documents.Paragraph pobj = (cellobj as Spire.Doc.Documents.Paragraph);
                                pobj.Text = arrtime[0];

                            }
                        }
                    }
                }
                else if (txt_type == 2)
                {
                    string[] arrtime = val.Split(' ');
                    if (arrtime.Length == 2)
                    {
                        SdtDropDownList stList = TagCell.SDTProperties.ControlProperties as SdtDropDownList;
                        string _v = "";
                        foreach (SdtListItem it in stList.ListItems)
                        {
                            if (it.DisplayText == arrtime[1])
                            {
                                _v = it.Value;
                                break;
                            }
                        }

                        if (_v != "")
                        {
                            if (TagCell.ChildObjects.Count == 1)
                            {
                                DocumentObject cellobj = TagCell.ChildObjects[0];
                                if (cellobj.DocumentObjectType == DocumentObjectType.Paragraph)
                                {
                                    Spire.Doc.Documents.Paragraph pobj = (cellobj as Spire.Doc.Documents.Paragraph);
                                    pobj.Text = arrtime[1];

                                }
                            }

                            //TextRange rt = new TextRange(sdoc);
                            //rt.Text = arrtime[1];
                            //Tag.ChildObjects.Clear();
                            //Tag.ChildObjects.Add(rt);
                        }
                    }
                }
            }

        }

        /// <summary>
        /// SetTagValue赋值操作
        /// </summary>
        /// <param name="template"></param>
        /// <param name="sdoc"></param>
        /// <param name="item"></param>
        public void SetTagValue(FrmPrintTemplate template, Spire.Doc.Document sdoc, ItemClass item)
        {
            StructureDocumentTag Tag = item.tag;
            string _lable = Tag.SDTProperties.Alias;
            string _tag = Tag.SDTProperties.Tag;
            //string _text = TagCell.SDTContent.Text;

            //CheckBoxItem$YangPinMingChen.0
            if (_tag.IndexOf("MetaData$") == -1) return;
            if (_tag.IndexOf("|Title") > 0) return;

            int txt_type = 0;
            string ctl_KeyOfEn = "";
            if (_tag.IndexOf("|Value") == -1)
            {
                if (_tag.IndexOf("|Data") == -1 && _tag.IndexOf("|Time") == -1)
                {
                    return;
                }
                else
                {
                    if (_tag.IndexOf("|Data") > 0)
                    {
                        ctl_KeyOfEn = _tag.Replace("MetaData$", "").Replace("|Data", "");
                        txt_type = 1;
                    }
                    if (_tag.IndexOf("|Time") > 0)
                    {
                        ctl_KeyOfEn = _tag.Replace("MetaData$", "").Replace("|Time", "");
                        txt_type = 2;
                    }
                }
            }
            else
            {
                ctl_KeyOfEn = _tag.Replace("MetaData$", "").Replace("|Value", "");
            }

            //编号错误跳过
            if (string.IsNullOrEmpty(ctl_KeyOfEn)) return;

            string val = "";
            val = this.HisDB.HisDBEles.GetValByKey(template.FrmID, ctl_KeyOfEn).AttrValue.ToString();
            if (string.IsNullOrEmpty(val))
            {
                //Tag.ChildObjects.Clear();
                TextRange rt = new TextRange(sdoc);
                rt.Text = " ";
                //Tag.ChildObjects.Clear();
                Tag.ChildObjects.Add(rt);
            }
            else
            {
                if (txt_type == 0)
                {
                    if (Tag.SDTProperties.SDTType == Spire.Doc.Documents.SdtType.Text)
                    {
                        if (Tag.ChildObjects.Count == 1)
                        {
                            DocumentObject cellobj = Tag.ChildObjects[0];
                            if (cellobj.DocumentObjectType == DocumentObjectType.Paragraph)
                            {
                                Spire.Doc.Documents.Paragraph pobj = (cellobj as Spire.Doc.Documents.Paragraph);
                                pobj.Text = val;

                            }
                        }
                    }

                    if (Tag.SDTProperties.SDTType == Spire.Doc.Documents.SdtType.DropDownList)
                    {
                        SdtDropDownList stList = Tag.SDTProperties.ControlProperties as SdtDropDownList;
                        string _v = "";
                        foreach (SdtListItem it in stList.ListItems)
                        {
                            if (it.DisplayText == val)
                            {
                                _v = it.Value;
                                break;
                            }
                        }

                        if (Tag.ChildObjects.Count == 1)
                        {
                            DocumentObject cellobj = Tag.ChildObjects[0];
                            if (cellobj.DocumentObjectType == DocumentObjectType.Paragraph)
                            {
                                Spire.Doc.Documents.Paragraph pobj = (cellobj as Spire.Doc.Documents.Paragraph);
                                pobj.Text = val;

                            }
                        }
                    }
                    if (Tag.SDTProperties.SDTType == Spire.Doc.Documents.SdtType.DatePicker)
                    {
                        string[] arrtime = val.Split(' ');
                        //TextRange rt = new TextRange(sdoc);
                        //rt.Text = arrtime[0];
                        //Tag.ChildObjects.Clear();
                        //Tag.ChildObjects.Add(rt);

                        if (Tag.ChildObjects.Count == 1)
                        {
                            DocumentObject cellobj = Tag.ChildObjects[0];
                            if (cellobj.DocumentObjectType == DocumentObjectType.Paragraph)
                            {
                                Spire.Doc.Documents.Paragraph pobj = (cellobj as Spire.Doc.Documents.Paragraph);
                                pobj.Text = arrtime[0];

                            }
                        }

                    }
                }
                else if (txt_type == 1)
                {
                    string[] arrtime = val.Split(' ');
                    if (Tag.SDTProperties.SDTType == Spire.Doc.Documents.SdtType.DatePicker)
                    {
                        if (Tag.ChildObjects.Count == 1)
                        {
                            DocumentObject cellobj = Tag.ChildObjects[0];
                            if (cellobj.DocumentObjectType == DocumentObjectType.Paragraph)
                            {
                                Spire.Doc.Documents.Paragraph pobj = (cellobj as Spire.Doc.Documents.Paragraph);
                                pobj.Text = arrtime[0];

                            }
                        }
                    }
                }
                else if (txt_type == 2)
                {
                    string[] arrtime = val.Split(' ');
                    if (arrtime.Length == 2)
                    {
                        SdtDropDownList stList = Tag.SDTProperties.ControlProperties as SdtDropDownList;
                        string _v = "";
                        foreach (SdtListItem it in stList.ListItems)
                        {
                            if (it.DisplayText == arrtime[1])
                            {
                                _v = it.Value;
                                break;
                            }
                        }

                        if (_v != "")
                        {
                            if (Tag.ChildObjects.Count == 1)
                            {
                                DocumentObject cellobj = Tag.ChildObjects[0];
                                if (cellobj.DocumentObjectType == DocumentObjectType.Paragraph)
                                {
                                    Spire.Doc.Documents.Paragraph pobj = (cellobj as Spire.Doc.Documents.Paragraph);
                                    pobj.Text = arrtime[1];

                                }
                            }

                            //TextRange rt = new TextRange(sdoc);
                            //rt.Text = arrtime[1];
                            //Tag.ChildObjects.Clear();
                            //Tag.ChildObjects.Add(rt);
                        }
                    }
                }
            }
        }

        public class EnumClass
        {
            public string No { get; set; }
            public string Lab { get; set; }
        }

        public bool CheckBoxCheck(string ctl_KeyOfEn, EnumClass ctl_EnumClass, StructureDocumentTagInline cBox) {
            bool IfCheck = false;
            if (cBox.SDTProperties.SDTType == Spire.Doc.Documents.SdtType.CheckBox)
            {
                //in_clt_value.Tag = "CheckBoxItem$" + attr.KeyOfEn + "." + No;
                string _tag = cBox.SDTProperties.Tag;
                if (_tag.IndexOf("CheckBoxItem$") == 0) {
                    string ctl_Str = _tag.Replace("CheckBoxItem$", "");
                    string[] ctl_arr = ctl_Str.Split('.');
                    if (ctl_arr.Length == 2) {
                        string ctl_Key = ctl_arr[0];
                        string ctl_No = ctl_arr[1];
                        if (ctl_Key == ctl_KeyOfEn) {
                            if (ctl_No == ctl_EnumClass.No) {
                                SdtCheckBox stCheck = cBox.SDTProperties.ControlProperties as SdtCheckBox;
                                stCheck.Checked = true;
                                IfCheck = true;
                            }
                        }
                    }
                }
            }
            return IfCheck;
        }
        /// <summary>
        /// SetTagCellValue赋值操作
        /// </summary>
        /// <param name="template"></param>
        /// <param name="sdoc"></param>
        /// <param name="Tag"></param>
        public void SetTagTableValue(FrmPrintTemplate template, Spire.Doc.Document sdoc, ItemClass item)
        {
            Spire.Doc.Table TagTable = item.tagTable;

            string Descr = TagTable.TableDescription;

            if (TagTable.Rows.Count == 1 && !string.IsNullOrEmpty(Descr))
            {
                try
                {
                    List<EnumClass> list = JsonConvert.DeserializeObject<List<EnumClass>>(Descr);
                    if (list.Count > 0)
                    {
                        string ctl_KeyOfEn = item.keyname;
                        string val = "";
                        var colitem = this.HisDB.HisDBEles.GetValByKey(template.FrmID, ctl_KeyOfEn);
                        val = colitem.AttrValue.ToString();

                        EnumClass ctl_EnumClass = null;
                        foreach (EnumClass enumclass in list)
                        {
                            if (enumclass.No == val || enumclass.Lab == val)
                            {
                                ctl_EnumClass = enumclass;
                                break;
                            }
                        }

                        if (ctl_EnumClass != null) {
                            foreach (Spire.Doc.TableRow row in item.tagTable.Rows)
                            {
                                foreach (Spire.Doc.TableCell cell in row.Cells)
                                {
                                    if (cell.DocumentObjectType == DocumentObjectType.TableCell)
                                    {
                                        foreach (DocumentObject cellobj in cell.ChildObjects)
                                        {
                                            if (cellobj.DocumentObjectType == DocumentObjectType.Paragraph)
                                            {
                                                foreach (DocumentObject pobj in (cellobj as Spire.Doc.Documents.Paragraph).ChildObjects)
                                                {
                                                    if (pobj.DocumentObjectType == DocumentObjectType.StructureDocumentTagInline)
                                                    {
                                                        StructureDocumentTagInline TagInline = pobj as StructureDocumentTagInline;
                                                        bool ischecked = CheckBoxCheck(ctl_KeyOfEn, ctl_EnumClass, TagInline);
                                                        if (ischecked) {
                                                            return;
                                                        }
                                                    }
                                                }
                                            }
                                            else if (cellobj.DocumentObjectType == DocumentObjectType.StructureDocumentTagInline)
                                            {
                                                StructureDocumentTagInline TagInline = cellobj as StructureDocumentTagInline;
                                                bool ischecked = CheckBoxCheck(ctl_KeyOfEn, ctl_EnumClass, TagInline);
                                                if (ischecked)
                                                {
                                                    return;
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
                catch (Exception ex)
                {

                }
            }
        }



        public void SetChildTableCellValue(DocumentObject cellobj, System.Data.DataTable dtable) {

            if (cellobj.DocumentObjectType == DocumentObjectType.StructureDocumentTagCell)
            {
                Spire.Doc.Documents.StructureDocumentTagCell item = (cellobj as Spire.Doc.Documents.StructureDocumentTagCell);

                string _tag = item.SDTProperties.Tag;
                if (_tag == null || _tag.IndexOf("MetaCol$") >= 0) return;

                int txt_type = 0;
                string name = "";
                if (_tag.IndexOf("|Value") == -1)
                {
                    if (_tag.IndexOf("|Data") == -1 && _tag.IndexOf("|Time") == -1)
                    {
                        return;
                    }
                    else
                    {
                        if (_tag.IndexOf("|Data") > 0)
                        {
                            name = _tag.Replace("MetaData$", "").Replace("|Data", "");
                            txt_type = 1;
                        }
                        if (_tag.IndexOf("|Time") > 0)
                        {
                            name = _tag.Replace("MetaData$", "").Replace("|Time", "");
                            txt_type = 2;
                        }
                    }
                }
                else
                {
                    name = _tag.Replace("MetaData$", "").Replace("|Value", "");
                }

                string[] arr = name.Split('.');
                if (arr.Length == 3)
                {
                    string rowindex = arr[1];
                    string KeyOfEn = arr[2];

                    if (rowindex == "1") return;//数据行从2开始
                    int s_row = int.Parse(rowindex) - 2;
                    if (s_row < 0) return;
                    string val = GetDataValue(dtable, KeyOfEn, int.Parse(rowindex) - 2);

                    if (!string.IsNullOrEmpty(val))
                    {
                        if (txt_type == 0)
                        {
                            if (item.SDTProperties.SDTType == Spire.Doc.Documents.SdtType.Text)
                            {
                                if (item.Paragraphs.Count == 1)
                                {
                                    item.Paragraphs[0].Text = val;
                                }
                            }
                            if (item.SDTProperties.SDTType == Spire.Doc.Documents.SdtType.DropDownList)
                            {
                                SdtDropDownList stList = item.SDTProperties.ControlProperties as SdtDropDownList;
                                string _v = "";
                                foreach (SdtListItem it in stList.ListItems)
                                {
                                    if (it.Value == val)
                                    {
                                        _v = it.DisplayText;
                                        break;
                                    }
                                }
                                if (_v != "")
                                {
                                    if (item.Paragraphs.Count == 1)
                                    {
                                        item.Paragraphs[0].Text = _v;
                                    }
                                }
                            }

                            if (item.SDTProperties.SDTType == Spire.Doc.Documents.SdtType.DatePicker)
                            {
                                if (item.Paragraphs.Count == 1)
                                {
                                    string[] arrtime = val.Split(' ');

                                    item.Paragraphs[0].Text = arrtime[0];
                                }
                            }
                        }
                        else if (txt_type == 1)
                        {
                            if (item.SDTProperties.SDTType == Spire.Doc.Documents.SdtType.DatePicker)
                            {
                                if (item.Paragraphs.Count == 1)
                                {
                                    string[] arrtime = val.Split(' ');
                                    item.Paragraphs[0].Text = arrtime[0];
                                }
                            }
                        }
                        else if (txt_type == 2)
                        {
                            string[] arrtime = val.Split(' ');
                            if (arrtime.Length == 2)
                            {
                                SdtDropDownList stList = item.SDTProperties.ControlProperties as SdtDropDownList;
                                string _v = "";
                                foreach (SdtListItem it in stList.ListItems)
                                {
                                    if (it.DisplayText == arrtime[1])
                                    {
                                        _v = it.Value;
                                        break;
                                    }
                                }
                                if (_v != "")
                                {
                                    if (item.Paragraphs.Count == 1)
                                    {
                                        item.Paragraphs[0].Text = arrtime[1];
                                    }

                                }
                            }

                        }
                    }
                    else {
                        if (item.Paragraphs.Count == 1)
                        {
                            item.Paragraphs[0].Text = "";
                        }
                    }
                }
            }   

        }

        /// <summary>
        /// SetChildTableValue赋值操作
        /// </summary>
        /// <param name="template"></param>
        /// <param name="sdoc"></param>
        /// <param name="Tag"></param>
        public void SetChildTableValue(FrmPrintTemplate template, Spire.Doc.Document sdoc, ItemClass item)
        {
            Spire.Doc.Table TagTable = item.tagTable;
            string _Title = TagTable.Title;
            if (!string.IsNullOrEmpty(_Title)&& _Title.IndexOf("MetaData$") == 0)
            {
                string tablename = _Title.Replace("MetaData$", "");
                if (!string.IsNullOrEmpty(tablename))
                {
                    DBDtl dbdtl = this.HisDB.HisDBDtls.GetValByKey(tablename);
                    if (dbdtl != null)
                    {
                        System.Data.DataTable dtable = dbdtl.HisDB;
                        foreach (Spire.Doc.TableRow row in TagTable.Rows) {
                            foreach (Spire.Doc.TableCell cell in row.Cells)
                            {
                                if (cell.DocumentObjectType == DocumentObjectType.StructureDocumentTagCell)
                                {
                                    //子表单元格赋值
                                    SetChildTableCellValue(cell, dtable);
                                }
                                /*
                                if (cell.DocumentObjectType == DocumentObjectType.TableCell)
                                {
                                    foreach (DocumentObject cellobj in cell.ChildObjects)
                                    {
                                        
                                    }
                                }*/
                            }

                        }
                        

                    }
                }
            }

        }

        public string GetDataValue(DataTable dt, string colname, int rowindex)
        {
            string result = "";
            if (dt != null && dt.Rows.Count > 0)
            {
                if (rowindex < dt.Rows.Count)
                {
                    DataRow[] drs = dt.Select("Idx='"+ rowindex + "'");
                    if (drs.Length > 0) {
                        result = drs[0][colname].ToString();
                    }
                    //result = dt.Rows[rowindex][colname].ToString();
                }
            }
            return result;
        }

        /// <summary>
        /// 字段类型
        /// </summary>
        public enum MyitemEnum
        {
            /// <summary>
            /// 主表一般字段
            /// </summary>
            TagInline = 0,
            /// <summary>
            /// 主表单选按钮
            /// </summary>
            TagCell = 1,
            /// <summary>
            /// 主表单选按钮
            /// </summary>
            Tag = 2,
            /// <summary>
            /// 主表单选按钮
            /// </summary>
            Table = 3,
            /// <summary>
            /// 2从表
            /// </summary>
            ChildTable = 4,
            /// <summary>
            /// 有子表
            /// </summary>
            HaveChild = 5
        }
        public class ItemClass
        {
            /// <summary>
            /// 初始化对象
            /// </summary>
            /// <param name="_keyname">字段编号</param>
            /// <param name="_type">字段类型   0 主表一般字段 1 主表表格内字段 2 主表单选按钮 3从表  </param>
            /// <param name="_taginline">StructureDocumentTagInline 参数</param>
            /// <param name="_tagCell">StructureDocumentTagCell  参数</param>
            /// <param name="_tagTable">Table  参数</param>
            public ItemClass(string _keyname, MyitemEnum _type, StructureDocumentTagInline _taginline = null, StructureDocumentTagCell _tagCell = null, StructureDocumentTag _tag = null, Spire.Doc.Table _tagTable = null)
            {
                keyname = _keyname;
                type = _type;
                taginline = _taginline;
                tagCell = _tagCell;
                tag = _tag;
                tagTable = _tagTable;
            }

            /// <summary>
            /// 字段名称
            /// </summary>
            public string keyname { get; set; }
            /// <summary>
            /// 0 主表一般字段 1 主表单选按钮 2从表
            /// </summary>
            public MyitemEnum type { get; set; }
            public StructureDocumentTagInline taginline { get; set; }
            public StructureDocumentTagCell tagCell { get; set; }
            public StructureDocumentTag tag { get; set; }
            public Spire.Doc.Table tagTable { get; set; }
        }
        public class DataItemClass
        {
            public DataItemClass()
            {
                tagItems = new List<ItemClass>();
            }
            public List<ItemClass> tagItems { get; set; }
        }

        /// <summary>
        /// 检测对象类型返回控件对象
        /// </summary>
        /// <param name="TagObj"></param>
        /// <returns></returns>
        public ItemClass GetItemObj(Spire.Doc.DocumentObject dObject)
        {
            ItemClass item = null;

            if (dObject.DocumentObjectType == Spire.Doc.Documents.DocumentObjectType.StructureDocumentTag) {
                Spire.Doc.Documents.StructureDocumentTag Tag = (dObject as Spire.Doc.Documents.StructureDocumentTag);
                if (Tag != null && Tag.SDTProperties != null && !string.IsNullOrEmpty(Tag.SDTProperties.Tag))
                {
                    string title = Tag.SDTProperties.Tag;
                    if (title.IndexOf("MetaData$") == 0)
                    {
                        string tag_str = title.Replace("MetaData$", "");
                        string[] str_arr = tag_str.Split('|');
                        if (str_arr.Length == 2 && (str_arr[1] == "Value" || str_arr[1] == "Data" || str_arr[1] == "Time"))
                        {
                            string key = str_arr[0];
                            item = new ItemClass(key, MyitemEnum.Tag, null, null, Tag, null);
                        }
                    }
                }
            }
            else if (dObject.DocumentObjectType == Spire.Doc.Documents.DocumentObjectType.StructureDocumentTagInline)
            {
                Spire.Doc.Documents.StructureDocumentTagInline TagInline = (dObject as Spire.Doc.Documents.StructureDocumentTagInline);
                if (TagInline != null && TagInline.SDTProperties != null && !string.IsNullOrEmpty(TagInline.SDTProperties.Tag))
                {
                    string title = TagInline.SDTProperties.Tag;
                    if (title.IndexOf("MetaData$") == 0)
                    {
                        string tag_str = title.Replace("MetaData$", "");
                        string[] str_arr = tag_str.Split('|');
                        if (str_arr.Length == 2 && (str_arr[1] == "Value" || str_arr[1] == "Data" || str_arr[1] == "Time"))
                        {
                            string key = str_arr[0];
                            item = new ItemClass(key, MyitemEnum.TagInline, TagInline, null, null, null);
                        }
                    }
                }
            }
            else if (dObject.DocumentObjectType == Spire.Doc.Documents.DocumentObjectType.StructureDocumentTagCell) {
                Spire.Doc.Documents.StructureDocumentTagCell TagCell = (dObject as Spire.Doc.Documents.StructureDocumentTagCell);
                if (TagCell != null && TagCell.SDTProperties != null && !string.IsNullOrEmpty(TagCell.SDTProperties.Tag))
                {
                    string title = TagCell.SDTProperties.Tag;
                    if (title.IndexOf("MetaData$") == 0)
                    {
                        string tag_str = title.Replace("MetaData$", "");
                        string[] str_arr = tag_str.Split('|');
                        if (str_arr.Length == 2 && (str_arr[1] == "Value" || str_arr[1] == "Data" || str_arr[1] == "Time"))
                        {
                            string key = str_arr[0];
                            item = new ItemClass(key, MyitemEnum.TagCell, null, TagCell, null, null);
                        }
                    }
                }
            }
            else if (dObject.DocumentObjectType == Spire.Doc.Documents.DocumentObjectType.Table)
            {
                Spire.Doc.Table TagTable = (dObject as Spire.Doc.Table);
                if (TagTable != null)
                {
                    //Spire.Doc.Table tb = dObject as Spire.Doc.Table;
                    //string title = TagTable.Title;
                    if (!string.IsNullOrEmpty(TagTable.Title))
                    {
                        if (TagTable.Title.IndexOf("MetaData$") == 0)
                        {
                            string key = TagTable.Title.Replace("MetaData$", "");
                            item = new ItemClass(key, MyitemEnum.ChildTable, null, null, null, TagTable);
                        }
                        if (TagTable.Title.IndexOf("CheckBoxData$") == 0)
                        {
                            string key = TagTable.Title.Replace("CheckBoxData$", "");
                            item = new ItemClass(key, MyitemEnum.Table, null, null, null, TagTable);
                        }
                    }
                    else
                    {
                        item = new ItemClass("", MyitemEnum.HaveChild, null, null, null, TagTable);
                    }
                }
            }
            return item;
        }

        public DataItemClass GetMainTags(Spire.Doc.Document document)
        {
            DataItemClass tags = new DataItemClass();
            
            foreach (Spire.Doc.Section section in document.Sections)
            {
                foreach (Spire.Doc.DocumentObject obj in section.Body.ChildObjects)
                {
                    if (obj.DocumentObjectType == Spire.Doc.Documents.DocumentObjectType.Paragraph)
                    {
                        foreach (Spire.Doc.DocumentObject pobj in (obj as Spire.Doc.Documents.Paragraph).ChildObjects)
                        {   
                            //Spire.Doc.Fields.TextRange
                            if (pobj.DocumentObjectType == Spire.Doc.Documents.DocumentObjectType.StructureDocumentTagInline){
                                ItemClass c_item = GetItemObj(pobj);
                                if (c_item != null)
                                {
                                    tags.tagItems.Add(c_item);
                                }
                            }
                        }
                    }
                    if (obj.DocumentObjectType == Spire.Doc.Documents.DocumentObjectType.Table)
                    {
                        DataItemClass ditem = GetChildTables(obj);
                        if (ditem != null)
                        {
                            foreach (ItemClass nitem in ditem.tagItems)
                            {
                                tags.tagItems.Add(nitem);
                            }
                        }
                    }
                }
            }
            return tags;
        }

        /// <summary>
        /// 表格检查 返回字段对象
        /// </summary>
        /// <param name="dObject"></param>
        /// <returns></returns>
        public DataItemClass GetChildTables(Spire.Doc.DocumentObject dObject)
        {
            DataItemClass tags = new DataItemClass();

            ItemClass c_item = GetItemObj(dObject);
            if (c_item != null)
            {
                if (c_item.type == MyitemEnum.HaveChild)
                {
                    foreach (Spire.Doc.TableRow row in c_item.tagTable.Rows)
                    {
                        foreach (Spire.Doc.TableCell cell in row.Cells)
                        {
                            if (cell.DocumentObjectType == DocumentObjectType.StructureDocumentTagCell)
                            {
                                ItemClass cell_item = GetItemObj(cell);
                                if (cell_item != null)
                                {
                                    tags.tagItems.Add(cell_item);
                                }
                            }
                            else if (cell.DocumentObjectType == DocumentObjectType.TableCell)
                            {
                                foreach (DocumentObject cellobj in cell.ChildObjects)
                                {
                                    if (cellobj.DocumentObjectType == Spire.Doc.Documents.DocumentObjectType.Paragraph)
                                    {
                                        foreach (Spire.Doc.DocumentObject pobj in (cellobj as Spire.Doc.Documents.Paragraph).ChildObjects)
                                        {
                                            if (pobj.DocumentObjectType == Spire.Doc.Documents.DocumentObjectType.StructureDocumentTagInline)
                                            {
                                                ItemClass tc_item = GetItemObj(pobj);
                                                if (tc_item != null)
                                                {
                                                    tags.tagItems.Add(tc_item);
                                                }
                                            }
                                        }
                                    }
                                    else if (cellobj.DocumentObjectType == Spire.Doc.Documents.DocumentObjectType.StructureDocumentTag)
                                    {
                                        ItemClass cell_item = GetItemObj(cellobj);
                                        if (cell_item != null)
                                        {
                                            tags.tagItems.Add(cell_item);
                                        }
                                    }
                                    else if (cellobj.DocumentObjectType == Spire.Doc.Documents.DocumentObjectType.Table)
                                    {
                                        DataItemClass ditem = GetChildTables(cellobj);
                                        if (ditem != null)
                                        {
                                            foreach (ItemClass nitem in ditem.tagItems)
                                            {
                                                tags.tagItems.Add(nitem);
                                            }
                                        }
                                    }

                                }
                            }

                        }
                    }
                }
                else
                {
                    //主表字段 主表单选字段 从表字段
                    tags.tagItems.Add(c_item);
                }                
            }

            return tags;
        }

        public string ContainsKey(Dictionary<string, List<string>> DtlMap, string key)
        {
            foreach (var kvp in DtlMap)
            {
                // Console.WriteLine($"Key = {kvp.Key}, Value = {kvp.Value}");
                if (kvp.Key.Split("@")[0].Equals(key))
                {
                    return kvp.Key;
                }
            }
            return "";
        }
        public string EnumKey(Dictionary<string, SysEnums> EnumMap, string key,string val)
        {
            //枚举字段
            if (EnumMap.ContainsKey(key))
            {
                SysEnums Enums = EnumMap[key];
                string rstr = "";
                foreach (SysEnum e in Enums){
                    string lab = e.Lab;
                    int IntKey = e.IntKey;
                    if (IntKey.ToString().Equals(val))
                    {
                        rstr= lab;
                    }
                }
                if (rstr.Equals(""))
                {
                    return val;
                }
                return rstr;
            }
            else
            {
                return val;
            }
        }
        public  Boolean DtlMapContainsKey(Dictionary<string, List<string>> DtlMap, String key)
        {
            // 遍历键
            foreach (var k in DtlMap.Keys)
            {
                if (k.StartsWith(key))
                {
                    return true;
                }
            }
            return false;
        }
        public void MakeExcelFile(string frmID, string templateFile, FrmPrintTemplate template, Int64 workID)
        {
            this.FrmID = frmID;
            this.HisFrmPrintTemplate = template;

            //生成数据源.
            this.HisDB = new BP.Sys.Printer.DB(template.FrmID, template.FrmID, template.MyPK, workID);

            #region 1. 打开excel准备工作.
            var workbook = new XLWorkbook(templateFile);
            var worksheet = workbook.Worksheet(1); // 获取第一个工作表
            //var value = worksheet.Cell("A1").GetString(); // 获取单元格 A1 的值
            #endregion 打开excel准备工作.

            #region 2. 从表及字段处理
            // 通过名称获取命名范围
            var namedRanges = workbook.NamedRanges;
            //获取从表区域  例子： Name: "Dict_XsTZ2Dict_xsTZ2Dtl1" Ranges:"sheet1!$s$10:$V$20"
            Dictionary<string, List<string>> DtlMap = new Dictionary<string, List<string>>();
            foreach (var namedRange in namedRanges)
            {
                string CellID = namedRange.Name;
                if (BP.DA.DataType.IsNullOrEmpty(CellID) == false && !CellID.Contains("."))
                {
                    string DtlName = CellID;
                    string range = namedRange.Ranges.ToString();
                    string sheet = range.Split("!")[0];
                    string DtlRange = range.Split("!")[1];
                    string name = DtlName + "@" + DtlRange;
                    List<string> list = new List<string>();
                    DtlMap.Add(name, list);
                }
            }

            //获取从表中的字段  例子：Name:"Dict_XsTZ2Dict_XsTZ2Dtl1.CanYuRenEmail"  Ranges:"sheet1!$s$10:$s$10"
            //主表，从表字段如果是枚举，单独记录
            Dictionary<string, SysEnums> EnumMap = new Dictionary<string, SysEnums>();
            foreach (var namedRange in namedRanges)
            {
                string CellID = namedRange.Name;
                if (BP.DA.DataType.IsNullOrEmpty(CellID) == false && CellID.Contains("."))
                {
                    string DtlName = CellID.Split(".")[0];
                    string key = CellID.Split(".")[1];
                    key = key.Substring(0, key.Length - 1);
                    string range = namedRange.Ranges.ToString();
                    string sheet = range.Split("!")[0];
                    string CellRange = range.Split("!")[1];
                    string name = key + "@" + CellRange.Split(":")[0];
                    string DtlMapKe = ContainsKey(DtlMap, DtlName);
                    //从表字段
                    if (!DtlMapKe.Equals(""))
                    {
                        List<string> list = new List<string>();
                        DtlMap.TryGetValue(DtlMapKe, out list);
                        if (list == null)
                        {
                            list = new List<string>();
                        }
                        list.Add(name);
                        DtlMap[DtlMapKe] = list;
                    }
                    //枚举的判断
                    MapAttr attr = new MapAttr();
                  
                    //从表key的最后 有索引值，需要特殊处理
                    if (DtlMapContainsKey(DtlMap, DtlName))
                    {
                        attr.MyPK = DtlName + "_" + key.Substring(0, key.Length - 1);
                    }
                    else
                    {
                        attr.MyPK = DtlName + "_" + key;
                    }
                    if (attr.RetrieveFromDBSources()==1)
                    {
                        string uibindkey = attr.UIBindKey;
                        if (uibindkey != null && !uibindkey.Equals(""))
                        {
                            BP.Sys.SysEnums myee = new SysEnums(uibindkey);
                            EnumMap.Add(CellID, myee);
                        }
                    }
                   
                }
            }
            #endregion 从表及字段处理.

            #region 3. 替换主表数据.   // 例子：Name:"frmweixiudan.xingming" ==>  frmid.key
            try
            {
                foreach (var namedRange in namedRanges)
                {
                    string cellID = namedRange.Name;
                    if (BP.DA.DataType.IsNullOrEmpty(cellID) == true || cellID.Contains(".") == false)
                        continue;
                    // 找到数据.
                    string[] strs = cellID.Split('.');
                    string attrFrmID = strs[0];
                    string attrKey = strs[1];
                    //不属于从表key
                    string DtlMapKe = ContainsKey(DtlMap, attrFrmID);
                    if (!DtlMapKe.Equals(""))
                        continue;
                    //获取值.
                    string val = this.HisDB.HisDBEles.GetValByKey(attrFrmID, attrKey).AttrValue.ToString();
                    //对该cell赋值.
                    // 获取命名范围的单元格
                    var range = namedRange.Ranges.First();
                    val = EnumKey(EnumMap, cellID, val);
                    // 赋值
                    range.Cell(1, 1).Value = val;

                }
            }
            catch (Exception ex)
            {
                throw new Exception("err@VSTO模版打印错误:" + ex.Message);
            }
           
            #endregion 2. 替换主表数据.

            #region 3. 替换从表数据.
            try
            {
                //循环从表
                foreach (var kvp in DtlMap)
                {

                    // Console.WriteLine($"Key = {kvp.Key}, Value = {kvp.Value}");
                    //Dict_XsTZ2Dict_xsTZ2Dtl1@$s$10:$V$20
                    string name = kvp.Key.Split("@")[0];

                    string sindexStr = kvp.Key.Split("@")[1].Split(":")[0];
                    string eindexStr = kvp.Key.Split("@")[1].Split(":")[1];
                    int sindex = int.Parse(sindexStr.Split("$")[2]);
                    int eindex = int.Parse(eindexStr.Split("$")[2]);
                    int csindex = ConvertLetter2Int(sindexStr.Split("$")[1]);
                    int ceindex = ConvertLetter2Int(eindexStr.Split("$")[1]);

                    //获取值.
                    DataTable dtable = this.HisDB.HisDBDtls.GetValByKey(name).HisDB;

                    MapDtl dtl = new MapDtl(name);
                    int VSTOEditModel= dtl.atPara.GetValIntByKey("VSTOEditModel");
                    int VSTOEditPara = dtl.atPara.GetValIntByKey("VSTOEditPara");

                    List<string> list = new List<string>();
                    DtlMap.TryGetValue(kvp.Key, out list);

                    var times = 1;
                    var tempKey = "";
                    List<string> keys = new List<string>();
                    for (int i = 0; i < list.Count; i++)
                    {
                        var key = list[i].Split("@")[0];
                        if (!keys.Contains(key))
                        {
                            keys.Add(key);
                        }

                        if (i == 0)
                        {
                            tempKey = key;
                        }
                        else
                        {
                            if (key == tempKey)
                            {
                                times++;
                            }
                        }
                    }

                    int rows = eindex - sindex;
                    int oneTimeCols = (ceindex - csindex + 1)/times;
                    int cols = oneTimeCols * 2;

                    //计算扩充的行
                    //int middlePara = rows * times;
                    //int rowsToInsert = 0;
                    //if ((dtable.Rows.Count - middlePara) % times == 0)
                    //{
                    //    rowsToInsert = (dtable.Rows.Count - middlePara) / times;
                    //}
                    //else
                    //{
                    //    rowsToInsert = (dtable.Rows.Count - middlePara) / times + 1;
                    //}

                    //if (rowsToInsert > 0)
                    //{
                    //    worksheet.Row(eindex).InsertRowsAbove(rowsToInsert);
                    //}

                    //int actualRow = rows + rowsToInsert;

                    int actualRow = rows;

                    //int mergCol1 = 0;
                    ////合并单元格
                    //for (int ii = csindex; ii <= (cols + csindex); ii++)
                    //{
                    //    int s1 = ii;
                    //    string tempCol = ConvertInt2Letter(ii);

                    //    string nkey = tempCol + sindex;
                    //    IXLCell cell = worksheet.Cell(nkey);
                    //    if (cell.IsMerged())
                    //    {
                    //        mergCol1 += cell.MergedRange().ColumnCount() - 1;
                    //        ii += mergCol1;
                    //        mergCol1 = 0;
                    //    }
                    //    int e1 = ii;

                    //    for (int rr = sindex + 1; rr <= (actualRow + sindex); rr++)
                    //    {
                    //        string key1 = ConvertInt2Letter(s1) + rr;
                    //        string key2 = ConvertInt2Letter(e1) + rr;
                    //        worksheet.Range(key1 + ":" + key2).Merge();
                    //    }
                    //}

                    //workbook.Save();


                    for (int j = 0; j < times; j++)
                    {
                        for (var r = 0; r < dtable.Rows.Count; r++)
                        {
                            DataRow row = dtable.Rows[r];
                            if (r >= actualRow * j && r < actualRow * (j + 1))
                            {
                                //知道数据在哪一行
                                var intRangeColumn = sindex + 1 + r % actualRow;
                                //再来找列范围

                                int mergCol2 = 0;
                                int keyCol = 0;
                                for (int ii = csindex + oneTimeCols * j; ii < (csindex + oneTimeCols * (j + 1)); ii++)
                                {
                                    string tempCol = ConvertInt2Letter(ii);
                                    string nkey = tempCol + intRangeColumn;
                                    IXLCell cell = worksheet.Cell(nkey);
                                    if (cell.IsMerged())
                                    {
                                        mergCol2 += cell.MergedRange().ColumnCount() - 1;
                                        ii += mergCol2;
                                        mergCol2 = 0;
                                    }
                                    string value = row[keys[keyCol]].ToString();
                                    value = EnumKey(EnumMap, name + "." + keys[keyCol], value);
                                    cell.SetValue(value);
                                    //设置水平居中对齐
                                    cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                                    // 设置垂直居中对齐
                                    cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                                    keyCol++;
                                }
                                keyCol = 0;

                            }
                            else
                            {
                                continue;
                            }

                        }

                    }


                }
                // 保存修改
                workbook.Save();
            }
            catch (Exception ex)
            {
                throw new Exception("err@VSTO模版打印错误:" + ex.Message);
            }
            finally
            {
                // 释放COM对象（重要！）
                worksheet = null;
                workbook = null;
                GC.Collect();
            }
            #endregion 3. 替换从表数据.
        }

        public string ConvertInt2Letter(int i)
        {
            if (i <= 26)
            {
                return Convert.ToChar(64 + i).ToString();
            }
            else
            {
                return Convert.ToChar(64 + (i / 26)).ToString() + Convert.ToChar((64 + (i % 26)));
            }
        }

        public int ConvertLetter2Int(string columnLetter)
        {
            if (string.IsNullOrEmpty(columnLetter))
                throw new ArgumentNullException(nameof(columnLetter));

            columnLetter = columnLetter.ToUpper();
            int columnNumber = 0;

            for (int i = 0; i < columnLetter.Length; i++)
            {
                char c = columnLetter[i];
                if (c < 'A' || c > 'Z')
                    throw new ArgumentException("无效的列字母: " + columnLetter);

                columnNumber = columnNumber * 26 + (c - 'A' + 1);
            }

            return columnNumber;
        }

    }


    public class StructureTags
    {
        List<Spire.Doc.Documents.StructureDocumentTagInline> m_tagInlines;
        public List<Spire.Doc.Documents.StructureDocumentTagInline> tagInlines
        {
            get
            {
                if (m_tagInlines == null)
                    m_tagInlines = new List<Spire.Doc.Documents.StructureDocumentTagInline>();
                return m_tagInlines;
            }
            set
            {
                m_tagInlines = value;
            }
        }
    }


}
